package database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import mockup.Mockup;
import mockup.UserMockup;
import server.database.ConnectionDatabase;

/**
 * @author daniel
 */

public class UserDB implements DatabaseAction {

	private String query;
	private Statement st;
	
	public UserDB() {
		try {
			st = ConnectionDatabase.getConnection().createStatement();			
		} catch (Exception e) {
			System.err.println("AppoitmentDB::Constructor:Exception: " + e.getMessage());
		}
	}

	public UserMockup get(int id) {
		if(id > -1) {
			query = "select user_id, name, dateOfBirth, email, phone, department from " 
				+ ConnectionDatabase.getNameDB() + ".person" +
			" where user_id = " + id;

			ResultSet rs = ConnectionDatabase.get(query, st);
			try {
				while (rs.next()) {
					int person_id = rs.getInt("user_id");
					String name = rs.getString("name");
					long dateOfBirth = rs.getLong("dateOfBirth");
					String email = rs.getString("email");
					String phone = rs.getString("phone");
					String department = rs.getString("department");

					UserMockup user = new UserMockup(person_id, name, dateOfBirth, email, phone, department);
					return user;
				}
			} catch (SQLException e ) {
				System.err.println("UserDB::get:SQLException: " + e.getMessage());
			}
		} else {
			System.err.println("0 > id , søker ikke i database");
		}
		return null;
	}

	public int insert(Mockup m) {
		int dbID = -1;
		UserMockup user = (UserMockup) m;
		if(0 > user.getId() ) {
			query = "INSERT into " + ConnectionDatabase.getNameDB()+".person (" +
			"name, dateOfBirth, email, phone, department) " +
			"values('" + user.getName() + "'," +
			user.getDateOfBirth() + ",'" + user.getEmail() + "','" + 
			user.getPhone() + "','" + user.getDepartment() + "')";

			dbID = ConnectionDatabase.insert(query);

		} else {
			update(m);
		}
		return dbID;
	}

	public void update(Mockup m){
		UserMockup user = (UserMockup) m;
		if(user.getId() > -1) {
			query = "UPDATE " + ConnectionDatabase.getNameDB() + ".person SET " +
			"name = '" + user.getName() + "'," +
			"dateOfBirth = " + user.getDateOfBirth() + "," + 
			"email = '" + user.getEmail() + "'," + 
			"phone = '" + user.getPhone() + "'," + 
			"department = '" + user.getDepartment() + "' " +
			"where user_id = " + user.getId();

			ConnectionDatabase.update(query, st);

		} else {
			System.err.println("Bruker har ikke ID; bruk insert i stedet");
		}
	}

	@Override
	public void delete(Mockup m) {
		UserMockup user = (UserMockup) m;
		if(user.getId() > -1) {
			query = "DELETE FROM " + ConnectionDatabase.getNameDB() + ".person " +
			"where user_id = " + user.getId();

			ConnectionDatabase.update(query, st);
		}
	}

	public List<UserMockup> getAll() {
		List<UserMockup> users = new ArrayList<UserMockup>();
		query = "select user_id, name, dateOfBirth, email, phone, department from " 
			+ ConnectionDatabase.getNameDB() + ".person";
		ResultSet rs = ConnectionDatabase.get(query, st);
		try {
			while (rs.next()) {
				int person_id = rs.getInt("user_id");
				String name = rs.getString("name");
				long dateOfBirth = rs.getLong("dateOfBirth");
				String email = rs.getString("email");
				String phone = rs.getString("phone");
				String department = rs.getString("department");

				UserMockup current = new UserMockup(person_id, name, dateOfBirth, email, phone, department);
				users.add(current);
			}
		} catch (SQLException e ) {
			System.err.println("UserDB::getAll:SQLException: " + e.getMessage());
			e.printStackTrace();
		}
		return users;
	}
	
	@Override
	public void emptyTable() {
		query = "TRUNCATE TABLE " + ConnectionDatabase.getNameDB() + ".setting";
		ConnectionDatabase.update(query, st);
		query = "TRUNCATE TABLE " + ConnectionDatabase.getNameDB() + ".person";
		ConnectionDatabase.update(query, st);
	}
}
